package com.lifeverse.repository;

import com.lifeverse.entity.DigitalDNA;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.math.BigDecimal;
import java.time.LocalDateTime;
import java.util.List;
import java.util.Optional;

/**
 * 数字DNA数据访问接口
 */
@Repository
public interface DigitalDNARepository extends JpaRepository<DigitalDNA, Long>, JpaSpecificationExecutor<DigitalDNA> {
    
    /**
     * 根据生命体ID查找DNA
     */
    Optional<DigitalDNA> findByLifeEntityIdAndDeletedFalse(Long lifeEntityId);
    
    /**
     * 根据特征哈希查找DNA
     */
    Optional<DigitalDNA> findByFeatureHashAndDeletedFalse(String featureHash);
    
    /**
     * 根据世代数查找DNA
     */
    List<DigitalDNA> findByGenerationAndDeletedFalse(Integer generation);
    
    /**
     * 根据适应度评分范围查找DNA
     */
    @Query("SELECT dna FROM DigitalDNA dna WHERE dna.fitnessScore BETWEEN :minFitness AND :maxFitness AND dna.deleted = false")
    List<DigitalDNA> findByFitnessScoreBetween(@Param("minFitness") BigDecimal minFitness, @Param("maxFitness") BigDecimal maxFitness);
    
    /**
     * 查找高适应度的DNA
     */
    @Query("SELECT dna FROM DigitalDNA dna WHERE dna.fitnessScore >= :minFitness AND dna.deleted = false ORDER BY dna.fitnessScore DESC")
    Page<DigitalDNA> findHighFitnessDNA(@Param("minFitness") BigDecimal minFitness, Pageable pageable);
    
    /**
     * 根据父DNA ID查找子DNA
     */
    List<DigitalDNA> findByParentDnaIdAndDeletedFalse(Long parentDnaId);
    
    /**
     * 根据母DNA ID查找子DNA
     */
    List<DigitalDNA> findByMotherDnaIdAndDeletedFalse(Long motherDnaId);
    
    /**
     * 查找稳定的DNA
     */
    @Query("SELECT dna FROM DigitalDNA dna WHERE dna.stabilityScore >= :minStability AND dna.deleted = false")
    List<DigitalDNA> findStableDNA(@Param("minStability") BigDecimal minStability);
    
    /**
     * 查找活跃的DNA
     */
    @Query("SELECT dna FROM DigitalDNA dna WHERE dna.expressionActivity >= :minActivity AND dna.deleted = false")
    List<DigitalDNA> findActiveDNA(@Param("minActivity") BigDecimal minActivity);
    
    /**
     * 查找最近变异的DNA
     */
    @Query("SELECT dna FROM DigitalDNA dna WHERE dna.lastMutationAt >= :since AND dna.deleted = false ORDER BY dna.lastMutationAt DESC")
    List<DigitalDNA> findRecentlyMutated(@Param("since") LocalDateTime since);
    
    /**
     * 查找最近表达的DNA
     */
    @Query("SELECT dna FROM DigitalDNA dna WHERE dna.lastExpressionAt >= :since AND dna.deleted = false ORDER BY dna.lastExpressionAt DESC")
    List<DigitalDNA> findRecentlyExpressed(@Param("since") LocalDateTime since);
    
    /**
     * 根据学习基因强度查找DNA
     */
    @Query("SELECT dna FROM DigitalDNA dna WHERE dna.learningGeneStrength >= :minStrength AND dna.deleted = false ORDER BY dna.learningGeneStrength DESC")
    List<DigitalDNA> findByLearningGeneStrength(@Param("minStrength") BigDecimal minStrength);
    
    /**
     * 根据创造力基因强度查找DNA
     */
    @Query("SELECT dna FROM DigitalDNA dna WHERE dna.creativityGeneStrength >= :minStrength AND dna.deleted = false ORDER BY dna.creativityGeneStrength DESC")
    List<DigitalDNA> findByCreativityGeneStrength(@Param("minStrength") BigDecimal minStrength);
    
    /**
     * 根据协作基因强度查找DNA
     */
    @Query("SELECT dna FROM DigitalDNA dna WHERE dna.collaborationGeneStrength >= :minStrength AND dna.deleted = false ORDER BY dna.collaborationGeneStrength DESC")
    List<DigitalDNA> findByCollaborationGeneStrength(@Param("minStrength") BigDecimal minStrength);
    
    /**
     * 根据适应性基因强度查找DNA
     */
    @Query("SELECT dna FROM DigitalDNA dna WHERE dna.adaptabilityGeneStrength >= :minStrength AND dna.deleted = false ORDER BY dna.adaptabilityGeneStrength DESC")
    List<DigitalDNA> findByAdaptabilityGeneStrength(@Param("minStrength") BigDecimal minStrength);
    
    /**
     * 根据情感基因强度查找DNA
     */
    @Query("SELECT dna FROM DigitalDNA dna WHERE dna.emotionGeneStrength >= :minStrength AND dna.deleted = false ORDER BY dna.emotionGeneStrength DESC")
    List<DigitalDNA> findByEmotionGeneStrength(@Param("minStrength") BigDecimal minStrength);
    
    /**
     * 根据逻辑基因强度查找DNA
     */
    @Query("SELECT dna FROM DigitalDNA dna WHERE dna.logicGeneStrength >= :minStrength AND dna.deleted = false ORDER BY dna.logicGeneStrength DESC")
    List<DigitalDNA> findByLogicGeneStrength(@Param("minStrength") BigDecimal minStrength);
    
    /**
     * 根据直觉基因强度查找DNA
     */
    @Query("SELECT dna FROM DigitalDNA dna WHERE dna.intuitionGeneStrength >= :minStrength AND dna.deleted = false ORDER BY dna.intuitionGeneStrength DESC")
    List<DigitalDNA> findByIntuitionGeneStrength(@Param("minStrength") BigDecimal minStrength);
    
    /**
     * 根据社交基因强度查找DNA
     */
    @Query("SELECT dna FROM DigitalDNA dna WHERE dna.socialGeneStrength >= :minStrength AND dna.deleted = false ORDER BY dna.socialGeneStrength DESC")
    List<DigitalDNA> findBySocialGeneStrength(@Param("minStrength") BigDecimal minStrength);
    
    /**
     * 统计各世代DNA数量
     */
    @Query("SELECT dna.generation, COUNT(dna) FROM DigitalDNA dna WHERE dna.deleted = false GROUP BY dna.generation ORDER BY dna.generation")
    List<Object[]> countByGeneration();
    
    /**
     * 获取平均适应度评分
     */
    @Query("SELECT AVG(dna.fitnessScore) FROM DigitalDNA dna WHERE dna.deleted = false")
    BigDecimal getAverageFitnessScore();
    
    /**
     * 获取平均稳定性评分
     */
    @Query("SELECT AVG(dna.stabilityScore) FROM DigitalDNA dna WHERE dna.deleted = false")
    BigDecimal getAverageStabilityScore();
    
    /**
     * 获取平均表达活跃度
     */
    @Query("SELECT AVG(dna.expressionActivity) FROM DigitalDNA dna WHERE dna.deleted = false")
    BigDecimal getAverageExpressionActivity();
    
    /**
     * 获取最高世代数
     */
    @Query("SELECT MAX(dna.generation) FROM DigitalDNA dna WHERE dna.deleted = false")
    Integer getMaxGeneration();
    
    /**
     * 查找需要变异的DNA（变异率高且稳定性低）
     */
    @Query("SELECT dna FROM DigitalDNA dna WHERE dna.mutationRate >= :minMutationRate AND dna.stabilityScore <= :maxStability AND dna.deleted = false")
    List<DigitalDNA> findDNAReadyForMutation(@Param("minMutationRate") BigDecimal minMutationRate, @Param("maxStability") BigDecimal maxStability);
    
    /**
     * 查找优秀的DNA（高适应度、高稳定性）
     */
    @Query("SELECT dna FROM DigitalDNA dna WHERE dna.fitnessScore >= :minFitness AND dna.stabilityScore >= :minStability AND dna.deleted = false ORDER BY dna.fitnessScore DESC, dna.stabilityScore DESC")
    List<DigitalDNA> findExcellentDNA(@Param("minFitness") BigDecimal minFitness, @Param("minStability") BigDecimal minStability);
    
    /**
     * 查找平衡的DNA（各基因强度相对均衡）
     */
    @Query("SELECT dna FROM DigitalDNA dna WHERE " +
           "ABS(dna.learningGeneStrength - dna.creativityGeneStrength) <= :tolerance AND " +
           "ABS(dna.learningGeneStrength - dna.collaborationGeneStrength) <= :tolerance AND " +
           "ABS(dna.learningGeneStrength - dna.adaptabilityGeneStrength) <= :tolerance AND " +
           "dna.deleted = false")
    List<DigitalDNA> findBalancedDNA(@Param("tolerance") BigDecimal tolerance);
}